<%@Page Language="VB"%>

<%@Import Namespace="System.Data" %>
<%@Import Namespace="System.Data.OleDb" %>

<%@ Register TagPrefix="wrox" TagName="connect" Src="..\global\connect-strings.ascx" %>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html><head>
<title>Managing Concurrent Updates with the RowUpdated Event</title>
<!-- #include file="..\global\style.inc" -->
</head>
<body bgcolor="#ffffff">
<span class="heading">Managing Concurrent Updates with the RowUpdated Event</span><hr />
<!--------------------------------------------------------------------------->

<%'-- insert connection string script --%>
<wrox:connect id="ctlConnectStrings" runat="server"/>

<div id="outError" runat="server">&nbsp;</div>

<b>Initial contents of the Books table:</b>
<asp:datagrid id="dgrResult1" runat="server" /><p />
<b>Contents of the Books table after editing:</b>
<asp:datagrid id="dgrResult2" runat="server" /><p />

<div id="outConcurrent" runat="server"></div><p />
<div id="outResult" runat="server"></div><p />

<b>Contents of the Errors table in a new DataSet after updating:</b>
<asp:datagrid id="dgrResult3" runat="server" /><p />

<b>Note:</b> while this page does execute the Update method of the DataSet, it does not<br />
actually change the original data. It uses a transaction that is rolled back afterwards.</br />
Otherwise, the update would prevent the code from running the next time.

<script language="vb" runat="server">

Dim gstrResult As String          'to hold the result messages
Dim gstrConnect As String         'to hold connection string
Dim gobjDataSet As DataSet        'to hold rows from database
Dim gobjErrorTable As DataTable   'to hold a list of errors
Dim gobjErrorDS As DataSet        'to hold the Errors table

Sub Page_Load()

   'get connection string from ..\global\connect-strings.ascx user control
   gstrConnect = ctlConnectStrings.OLEDBConnectionString

   'specify the SELECT statement to extract the data
   Dim strSelect As String
   strSelect = "SELECT * FROM BookList WHERE ISBN LIKE '18610022%'"

   'create a new DataSet object
   gobjDataSet = New DataSet()

   'create a new Connection object using the connection string
   Dim objConnect As New OleDbConnection(gstrConnect)

   'create a new DataAdapter using the connection object and select statement
   Dim objDataAdapter As New OleDbDataAdapter(strSelect, objConnect)

   Try

      'fill the dataset with data using the DataAdapter object
      objDataAdapter.Fill(gobjDataSet, "Books")

   Catch objError As Exception

      'display error details
      outError.innerHTML = "<b>* Error while accessing data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   'accept the changes to "fix" the current state of the DataSet contents
   gobjDataSet.AcceptChanges()

   'declare a variable to reference the Books table
   Dim objTable As DataTable = gobjDataSet.Tables("Books")

   'display the contents of the Books table before changing data
   dgrResult1.DataSource = objTable.DefaultView
   dgrResult1.DataBind()   'and bind (display) the data

   'now change some records in the Books table
   objTable.Rows(0).Delete()
   objTable.Rows(1)("Title") = "Amateur Theatricals for Windows 2000"
   objTable.Rows(2).Delete()
   objTable.Rows(3).Delete()
   objTable.Rows(4)("PublicationDate") = "01-01-2002"
   objTable.Rows.RemoveAt(5)
   'notice that using the Remove method on row 5 (rather than marking
   'it as deleted) means that the next row then becomes row 5
   objTable.Rows(5)("ISBN") = "200000000"

   'add a new row using an array of values
   Dim objValsArray(2) As Object
   objValsArray(0) = "200000001"
   objValsArray(1) = "Impressionist Guide to Painting Computers"
   objValsArray(2) = "05-02-2002"
   objTable.Rows.Add(objValsArray)

   'display the contents of the Books table after changing the data
   dgrResult2.DataSource = objTable.DefaultView
   dgrResult2.DataBind()   'and bind (display) the data

   '-------------------------------------------------------------------
   'change some values in the original table while the DataSet is holding
   'a disconnected copy of the data to force a concurrency error

   Dim strConcurrent, strUpdate As String
   Dim intRowsAffected As Integer

   'need a new (separate) Connection and Command object
   Dim objNewConnect As New OleDbConnection(gstrConnect)
   Dim objNewCommand As New OleDbCommand()
   objNewCommand.Connection = objNewConnect

   Try

      objNewConnect.Open()

      'modify two of the book titles to force concurrency errors
      strConcurrent += "<b>Concurrently executed</b>:<br />"
      Dim datNow As DateTime = Now()
      Dim strNow As String = datNow.ToString("dd-M-yy \a\t hh:mm:ss")
      strUpdate = "UPDATE BookList SET Title = 'New Book Written on " _
                & strNow & "' WHERE ISBN = '186100222X'"
      objNewCommand.CommandText = strUpdate
      intRowsAffected = objNewCommand.ExecuteNonQuery()
      strConcurrent += strUpdate & "<br /> ... <b>" _
             & CStr(intRowsAffected) & "</b> row(s) affected<br />"

      'edit the next line to force a concurrency error if none occurs
      strUpdate = "UPDATE BookList SET Title = 'Another Book Written on " _
                & strNow & "' WHERE ISBN = '1861002262'"
      objNewCommand.CommandText = strUpdate
      intRowsAffected = objNewCommand.ExecuteNonQuery()
      strConcurrent += strUpdate & "<br /> ... <b>" _
             & CStr(intRowsAffected) & "</b> row(s) affected<br />"

      objNewConnect.Close()

   Catch objError As Exception

      'display error details
      outError.InnerHtml = "<b>* Error while updating original data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   outConcurrent.InnerHtml = strConcurrent

   '----------------------------------------------------------------

   'now set up event handler to react to RowUpdated event
   AddHandler objDataAdapter.RowUpdated, _
      New OleDbRowUpdatedEventHandler(AddressOf OnRowUpdated)

   '----------------------------------------------------------------

   'create a new empty Table object to hold error rows
   gobjErrorTable = New DataTable("Errors")

   'define the columns for the Errors table
   gobjErrorTable.Columns.Add("Action", System.Type.GetType("System.String"))
   gobjErrorTable.Columns.Add("RowKey", System.Type.GetType("System.String"))
   gobjErrorTable.Columns.Add("ColumnName", System.Type.GetType("System.String"))
   gobjErrorTable.Columns.Add("OriginalValue", System.Type.GetType("System.String"))
   gobjErrorTable.Columns.Add("CurrentValue", System.Type.GetType("System.String"))
   gobjErrorTable.Columns.Add("DatabaseValue", System.Type.GetType("System.String"))

   'create a new empty DataSet object to hold Errors table
   gobjErrorDS = New DataSet()
   gobjErrorDS.Tables.Add(gobjErrorTable)

   '----------------------------------------------------------------

   'declare a variable to hold a Transaction object
   Dim objTransaction As OleDbTransaction

   Try

      'create an auto-generated command builder to create the commands
      'to update, insert and delete the data
      Dim objCommandBuilder As New OleDbCommandBuilder(objDataAdapter)

      'set the update, insert and delete commands for the DataAdapter
      objDataAdapter.DeleteCommand = objCommandBuilder.GetDeleteCommand()
      objDataAdapter.InsertCommand = objCommandBuilder.GetInsertCommand()
      objDataAdapter.UpdateCommand = objCommandBuilder.GetUpdateCommand()

      'start a transaction so that we can roll back the changes
      'must do this on an open Connection object
      objConnect.Open()
      objTransaction = objConnect.BeginTransaction()

      'attach the current transaction to all the Command objects
      'must be done after setting Connection property
      objDataAdapter.DeleteCommand.Transaction = objTransaction
      objDataAdapter.InsertCommand.Transaction = objTransaction
      objDataAdapter.UpdateCommand.Transaction = objTransaction

      'perform the update on the original data
      objDataAdapter.Update(gobjDataSet, "Books")

   Catch objError As Exception

      'rollback the transaction undoing any updates
      objTransaction.Rollback()

      'display error details
      outError.innerHTML = "<b>* Error while updating original data</b>.<br />" _
          & objError.Message & "<br />" & objError.Source
      Exit Sub  ' and stop execution

   End Try

   'to actually update the source tables just change the next line
   'to CommitTransaction() or remove the transaction altogether.
   'note that you will then have to change the SELECT statement and
   'code to be able to run the example again, or rebuild the
   'original data from the supplied SQL scripts
   objTransaction.Rollback()

   'display the results of the Update
   outResult.InnerHtml = gstrResult

   'display the contents of the Errors table
   dgrResult3.DataSource = gobjErrorDS
   dgrResult3.DataMember = "Errors"
   dgrResult3.DataBind()   'and bind (display) the data


End Sub

'----------------------------------------------------------------

'event handler for the RowUpdated event
Sub OnRowUpdated(objSender As Object, objArgs As OleDbRowUpdatedEventArgs)

   'see if the update was successful
   If objArgs.RecordsAffected < 1 Then

      'get the text description of the StatementType
      Dim strType = System.Enum.GetName(objArgs.StatementType.GetType(), _
                                        objArgs.StatementType)

      'get the primary key of the row (the ISBN)
      Dim strRowKey As String
      strRowKey = objArgs.Row("ISBN", DataRowVersion.Original)

      'get a reference to the original table in the DataSet
      Dim objTable As DataTable = gobjDataSet.Tables(0)

      Dim objColumn As DataColumn    'to hold a DataColumn object
      Dim strColumnName As String    'to hold the column name

      'iterate through the columns in the current row
      For Each objColumn In objTable.Columns

         'get the column name as a string
         strColumnName = objColumn.ColumnName

         'see if this column has been modified
         If objArgs.Row(strColumnName, DataRowVersion.Current) _
            <> objArgs.Row(strColumnName, DataRowVersion.Original) Then

            'create a new DataRow object instance in this table
            Dim objDataRow As DataRow = gobjErrorTable.NewRow()

            'and fill in the values
            objDataRow("Action") = strType
            objDataRow("RowKey") = strRowKey
            objDataRow("ColumnName") = strColumnName
            objDataRow("OriginalValue") = objArgs.Row(strColumnName, DataRowVersion.Original)
            objDataRow("CurrentValue") = objArgs.Row(strColumnName, DataRowVersion.Current)
            objDataRow("DatabaseValue") = GetCurrentColumnValue(gstrConnect, strRowKey, strColumnName)

            'add new row to the Errors table
            gobjErrorTable.Rows.Add(objDataRow)

         End If

      Next

      'set the Status property of the row to skip current row update if there is an error
      'default is Continue which means an error will halt execution of following updates
      objArgs.Status = UpdateStatus.SkipCurrentRow

   End If

End Sub

'----------------------------------------------------------------

Function GetCurrentColumnValue(strConnect As String, strISBN As String, strColumnName As String) As String

   'select the existing column value from the underlying table in the database
   Dim strSQL = "SELECT " & strColumnName _
              & " FROM BookList WHERE ISBN='" & strISBN & "'"
   Dim objConnect As New OleDbConnection(strConnect)
   Dim objCommand As New OleDbCommand(strSQL, objConnect)
   Try
      objConnect.Open()
      'use ExecuteScalar for efficiency, it returns only one item
      'get the value direct from it and convert to a String
      GetCurrentColumnValue = objCommand.ExecuteScalar().ToString()
      objConnect.Close()
   Catch objError As Exception
      GetCurrentColumnValue = "*Error*"
   End Try

End Function

</script>

<!--------------------------------------------------------------------------->
<!-- #include file="..\global\foot.inc" -->
</body>
</html>
